/**
 *
 */
package com.elcom.emocbc.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.elcom.emocbc.cbm.BroadcastDTO;
import com.elcom.emocbc.cbm.CellBroadcastService;
import com.elcom.emocbc.cbm.MenuItemDTO;

/**
 * @author tiamo114
 *
 */
public class CellBroadcastDAO {

    private final Log LOG = LogFactory.getLog(CellBroadcastDAO.class);
    public static String SQL_GET_LIST_BROADCAST = "SELECT a.broadcasttime_id, a.zone_id, a.createdby, a.createddate, d.program_id, b.topic_id, c.zonename, a.starttime, a.program_indexs, a.cp_topic_id FROM broadcasttime a, cp_topic b, zones c, programs d where a.zone_id=c.zone_id and a.status=0 and d.status=1 and a.cp_topic_id=b.cp_topic_id and (a.cp_topic_id=d.cps_topic_id and a.program_indexs=d.indexs) and to_date(TO_CHAR(SYSDATE,'dd/MM/yyyy')||' '||a.starttime,'dd/MM/yyyy hh24:mi') between SYSDATE and SYSDATE+(10/1440) FOR UPDATE";
    private static final String SQL_CALCULATE_TIME = "select * from (SELECT * FROM broadcasttime a where a.starttime>? order by a.starttime) where ROWNUM=1";
    private static final String SQL_GET_LIST_MENU = "SELECT * FROM menu_items a where a.parent_id=? order by a.indexs";
    private static final String SQL_GET_LIST_PROGRAM = "SELECT a.program_id FROM programs a where a.status=1 and a.cps_topic_id=? and a.indexs=?";
    private static final String SQL_GET_DISPLAY_CONTENT = "SELECT a.subcontents FROM programs a where a.program_id=?";
    private static final String SQL_GET_BROADCAST_HIST_SEQ = "select broadcast_hist_seq.NEXTVAL from dual";
    private static final String SQL_INSERT_HISTORY_BROADCAST = "insert into broadcast_hist(broadcasttime_id,program_indexs,cp_topic_id,zone_id,starttime,status,createdby,createddate,subcontents,broadcast_hist_id) values(?,?,?,?,?,?,?,?,?,?)";
    private static final String SQL_INSERT_BROADCAST_CBC_TRANSACTION_MAP = "insert into CBC_TRANSACTION_MAP(broadcast_id,cbc_transaction_id, created_time) values(?,?, to_date(?, 'dd-mm-yyyy hh24:mi:ss'))";
    private static final String SQL_UPDATE_STATE_BROADCAST = "update broadcasttime a set a.status=? where a.broadcasttime_id=?";
//    private static final String SQL_UPDATE_STATE_BROADCAST_HIST = "update broadcast_hist a set a.status=? where a.broadcast_hist_id=?";
    private static final String SQL_GET_LIST_ALL_MENU = "SELECT * FROM menu_items a where a.program_id=?";
//    private static final String SQL_NEXT_BROADCAST_HIST_SEQ = "SELECT broadcast_hist_seq.NEXTVAL FROM dual";
    private static final String SQL_INSERT_MENU_HIST = "insert into menu_items_hist(MENUITEMS_ID,PARENT_ID,BROADCAST_HIST_ID,ACTIONTYPE,SYNTAX,CONTENT,SHORTCODE,LEVELS,TITLE,PROGRAM_ID,INDEXS) values(?,?,?,?,?,?,?,?,?,?,?)";
    private SimpleDateFormat f_short = null;
    private SimpleDateFormat f_full = null;

    public CellBroadcastDAO() {
        f_short = new SimpleDateFormat("dd/MM/yyyy");
        f_full = new SimpleDateFormat("dd/MM/yyyy HH:mm");
    }

    public boolean updateStateBroadcast(Connection _conn, BroadcastDTO broadcast, int state) {
        Connection conn = _conn;
        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(SQL_UPDATE_STATE_BROADCAST);
            ps.setInt(1, state);
            ps.setInt(2, broadcast.getBroadcastID());

            int i = ps.executeUpdate();
            if (i > 0) {
                LOG.debug("Update OK with new state: " + state);
                return true;
            } else {
                LOG.warn("Cannot update with new state: " + state);
            }

        } catch (Exception e) {
            LOG.error("Exception when updateStateBroadcast to DB: ", e);
        } finally {
            try {
                ps.close();
            } catch (SQLException e) {
                LOG.error("Exception when close PreparedStatement in DB: ", e);
            }
        }

        return false;
    }

    public int calculteTimeDistance(String startTime) {
        int rsTime = 60; // default la 1phut
        DatabaseConnection dbConnector = DatabaseConnection.newInstance();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = dbConnector.getConn();
            ps = conn.prepareStatement(SQL_CALCULATE_TIME);
            ps.setString(1, startTime);
            rs = ps.executeQuery();

            if (rs.next()) {
                String endTime = rs.getString("starttime");
                String start = f_short.format(new Date(System.currentTimeMillis())) + " " + startTime;
                String end = f_short.format(new Date(System.currentTimeMillis())) + " " + endTime;

                Date startDate = f_full.parse(start);
                Date endDate = f_full.parse(end);

                rsTime = (int) ((endDate.getTime() - startDate.getTime()) / 1000);
            }


        } catch (Exception e) {
            LOG.error("Exception when calculate distance time from DB: ", e);
        } finally {
            dbConnector.closeResultSet(rs);
            dbConnector.closePreparedStatement(ps);
            dbConnector.closeConn(conn);
        }

        return rsTime;
    }

    public List<BroadcastDTO> getListBroadcast() {
        LOG.debug("Test broadcast from DB");
        List<BroadcastDTO> list = new ArrayList<BroadcastDTO>();
        DatabaseConnection dbConnector = DatabaseConnection.newInstance();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        LOG.debug("Get broadcast from DB");

        try {
            conn = dbConnector.getConn();
            stmt = conn.createStatement();
//			stmt.setQueryTimeout(3);
            rs = stmt.executeQuery(SQL_GET_LIST_BROADCAST);
            while (rs.next()) {
                BroadcastDTO broadcast = new BroadcastDTO();
                broadcast.setTopicID(rs.getInt("topic_id"));
                broadcast.setProgramID(rs.getString("program_id"));
                broadcast.setZone(rs.getString("zonename"));
                String start = f_short.format(new Date(System.currentTimeMillis())) + " " + rs.getString("starttime");
                Date startDate = f_full.parse(start);
                broadcast.setStartDate(startDate);
                broadcast.setStartTime(rs.getString("starttime"));
                broadcast.setEndDate(new Date(startDate.getTime() + 60000l));
                broadcast.setCpID(rs.getInt("cp_topic_id"));
                broadcast.setIndex(rs.getInt("program_indexs"));

                broadcast.setBroadcastID(rs.getInt("broadcasttime_id"));
                broadcast.setZoneID(rs.getInt("zone_id"));
                broadcast.setCreatedBy(rs.getString("createdby"));
                broadcast.setCreatedDate(rs.getDate("createddate"));
                if (updateStateBroadcast(conn, broadcast, CellBroadcastService.PROCESSING)) {
                    list.add(broadcast);
                    LOG.info("Get broadcast request from DB and update state: " + broadcast.toString());
                }
            }
            conn.commit();
            LOG.debug("Finish Get broadcast from DB");
        } catch (Exception e) {
            LOG.error("Exception when get list broadcast from DB: ", e);
        } finally {
            dbConnector.closeResultSet(rs);
            dbConnector.closeStatement(stmt);
            dbConnector.closeConn(conn);
        }

        return list;
    }

    public List<MenuItemDTO> getListMenuItem(int topicID, String programID) {
        List<MenuItemDTO> list = new ArrayList<MenuItemDTO>();
        DatabaseConnection dbConnector = DatabaseConnection.newInstance();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = dbConnector.getConn();
            ps = conn.prepareStatement(SQL_GET_LIST_MENU);
            ps.setString(1, programID);
            rs = ps.executeQuery();

            while (rs.next()) {
                int shortcode = rs.getInt("shortcode");
                String type;

                String content = rs.getString("syntax");
                if (content == null) {
                    content = programID;
                    type = "B3";
                } else {
                    type = "A3";
                }

                MenuItemDTO menu = new MenuItemDTO(topicID, programID, type, shortcode, content, rs.getString("title"));
                list.add(menu);
                LOG.info("Get menuItem from DB: " + menu.toString());
            }

        } catch (Exception e) {
            LOG.error("Exception when get list menuItem from DB with programID-" + programID + " and topicID-" + topicID + ": ", e);
        } finally {
            dbConnector.closeResultSet(rs);
            dbConnector.closePreparedStatement(ps);
            dbConnector.closeConn(conn);
        }

        return list;
    }

    public LinkedList<String> getListProgramID(int topicID) {
        LinkedList<String> list = new LinkedList<String>();
        DatabaseConnection dbConnector = DatabaseConnection.newInstance();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = dbConnector.getConn();
            ps = conn.prepareStatement(SQL_GET_LIST_PROGRAM);
            ps.setInt(1, topicID);
            rs = ps.executeQuery();
            while (rs.next()) {
                String programID = rs.getString("program_id");
                list.add(programID);
                LOG.info("Get broadcast programID from DB: " + programID);
            }
        } catch (Exception e) {
            LOG.error("Exception when get broadcast programID from DB: ", e);
        } finally {
            dbConnector.closeResultSet(rs);
            dbConnector.closePreparedStatement(ps);
            dbConnector.closeConn(conn);
        }

        return list;
    }

    public String getDisplayContent(String programID) {
        String result = "";
        DatabaseConnection dbConnector = DatabaseConnection.newInstance();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = dbConnector.getConn();
            ps = conn.prepareStatement(SQL_GET_DISPLAY_CONTENT);
            ps.setString(1, programID);
            rs = ps.executeQuery();
            if (rs.next()) {
                result = rs.getString("subcontents");
            }
        } catch (Exception e) {
            LOG.error("Exception when get display content from DB: ", e);
        } finally {
            dbConnector.closeResultSet(rs);
            dbConnector.closePreparedStatement(ps);
            dbConnector.closeConn(conn);
        }
        return result;
    }

    public boolean insertBroadcastHist(BroadcastDTO broadcast, String subContent, int result) {
        DatabaseConnection dbConnector = DatabaseConnection.newInstance();
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            int broadcastHistId = getBroadcastHistSeq();
            LOG.debug("Got new broadcast_hist_id = " + broadcastHistId);

            conn = dbConnector.getConn();
            ps = conn.prepareStatement(SQL_INSERT_HISTORY_BROADCAST);
            ps.setInt(1, broadcast.getBroadcastID());
            ps.setInt(2, broadcast.getIndex());
            ps.setInt(3, broadcast.getCpID());
            ps.setInt(4, broadcast.getZoneID());
            ps.setTimestamp(5, new Timestamp(broadcast.getStartDate().getTime()));
            ps.setInt(6, result);
            ps.setString(7, broadcast.getCreatedBy());
            ps.setDate(8, broadcast.getCreatedDate());
            ps.setString(9, subContent);
            ps.setInt(10, broadcastHistId);
            int i = ps.executeUpdate();
            conn.commit();

            insertBroadcastMenu(broadcast, subContent, broadcastHistId);

            if (i > 0) {
                LOG.info("Insert insertBroadcastHist OK with programID: " + broadcast.toString());
                return true;
            } else {
                LOG.warn("Cannot insertBroadcastHist with programID: " + broadcast.toString());
            }

        } catch (Exception e) {
            LOG.error("Exception when insertBroadcastHist to DB: ", e);
        } finally {
            dbConnector.closePreparedStatement(ps);
            dbConnector.closeConn(conn);
        }

        return false;
    }

    public void insertBroadcastMenu(BroadcastDTO broadcast, String subContent, int broadcastHistId) {
        DatabaseConnection dbConnector = DatabaseConnection.newInstance();
        Connection conn = null;
        PreparedStatement ps = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            conn = dbConnector.getConn();
            psmt = conn.prepareStatement(SQL_GET_LIST_ALL_MENU);
            psmt.setString(1, broadcast.getProgramID());
            rs = psmt.executeQuery();
            while (rs.next()) {
                try {
                    ps = conn.prepareStatement(SQL_INSERT_MENU_HIST);
                    ps.setString(1, rs.getString("MENUITEMS_ID"));
                    ps.setString(2, rs.getString("PARENT_ID"));
                    ps.setInt(3, broadcastHistId);
                    ps.setString(4, rs.getString("ACTIONTYPE"));
                    String syntax = "";
                    if (rs.getString("SYNTAX") != null) {
                        syntax = rs.getString("SYNTAX");
                    }
                    ps.setString(5, syntax);

                    String content = "";
                    if (rs.getString("CONTENT") != null) {
                        content = rs.getString("CONTENT");
                    }
                    ps.setString(6, content);

                    ps.setString(7, rs.getString("SHORTCODE"));
                    ps.setInt(8, rs.getInt("LEVELS"));
                    ps.setString(9, rs.getString("TITLE"));
                    ps.setString(10, broadcast.getProgramID());
                    ps.setInt(11, rs.getInt("INDEXS"));

                    int i = ps.executeUpdate();
                    conn.commit();
                    if (i > 0) {
                        LOG.info("Insert OK menu history for broadcast: " + broadcast.toString());
                    } else {
                        LOG.info("Cannot insert menu history for broadcast: " + broadcast.toString());
                    }
                } catch (Exception e) {
                    LOG.error("Exception when insertBroadcastMenu to DB: ", e);
                } finally {
                    dbConnector.closePreparedStatement(ps);
                    ps = null;
                }
            }
        } catch (Exception e) {
            LOG.error("Exception when get all Menu from DB: ", e);
        } finally {
            dbConnector.closeResultSet(rs);
            dbConnector.closePreparedStatement(psmt);
            dbConnector.closeConn(conn);
        }
    }

    public boolean updateStateBroadcast(BroadcastDTO broadcast, int state) {
        DatabaseConnection dbConnector = DatabaseConnection.newInstance();
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = dbConnector.getConn();
            ps = conn.prepareStatement(SQL_UPDATE_STATE_BROADCAST);
            ps.setInt(1, state);
            ps.setInt(2, broadcast.getBroadcastID());

            int i = ps.executeUpdate();
            conn.commit();
            if (i > 0) {
                LOG.debug("Update OK with new state: " + state);
                return true;
            } else {
                LOG.warn("Cannot update with new state: " + state);
            }

        } catch (Exception e) {
            LOG.error("Exception when updateStateBroadcast to DB: ", e);
        } finally {
            dbConnector.closePreparedStatement(ps);
            dbConnector.closeConn(conn);
        }

        return false;
    }

    private int getBroadcastHistSeq() throws SQLException {
        int result;
        DatabaseConnection dbConnector = DatabaseConnection.newInstance();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = dbConnector.getConn();
            ps = conn.prepareStatement(SQL_GET_BROADCAST_HIST_SEQ);
            rs = ps.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
                return result;
            } else {
                throw new SQLException("Cannot get next seq for broadcast_hist_id");
            }

        } finally {
            dbConnector.closeResultSet(rs);
            dbConnector.closePreparedStatement(ps);
            dbConnector.closeConn(conn);
        }

    }

    public void insertCbcTransactionMap(int broadcastId, String cbcTransactionId) {
        DatabaseConnection dbConnector = DatabaseConnection.newInstance();
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = dbConnector.getConn();
            ps = conn.prepareStatement(SQL_INSERT_BROADCAST_CBC_TRANSACTION_MAP);
            ps.setInt(1, broadcastId);
            ps.setString(2, cbcTransactionId);
            SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
            ps.setString(3, sdf.format(new Date()));

            int i = ps.executeUpdate();
            conn.commit();
            if (i > 0) {
                LOG.info("Insert OK Cbc transactionID " + cbcTransactionId + " for broadcast: " + broadcastId);
            } else {
                LOG.info("Cannot insert Cbc transactionID " + cbcTransactionId + " for broadcast: " + broadcastId);
            }

        } catch (Exception e) {
            LOG.error("Exception when insertCbcTransactionMap to DB: ", e);
        } finally {
            dbConnector.closePreparedStatement(ps);
            dbConnector.closeConn(conn);
        }
    }
}
